package re.kitri.common.game;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import re.kitri.common.DBConnect.DBConnManager;

public class GameImple {

	//�Խ��� ��ü �� ���� ��ȯ   
	public int totRecord(GameDTO gameDTO) {
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count = 0;
		String sql = "select count(game_seq)as count from game_schedule";

		if(gameDTO.getSelectpage().equals("game_schedule")){
			sql = "select count(game_seq)as count from game_schedule";
		}else if(gameDTO.getSelectpage().equals("bet_select")){
			sql = "select count(game_seq)as count from game_schedule where game_regdate >= sysdate";
		}else if(gameDTO.getSelectpage().equals("bet_info")){
			sql = "select count(game_seq)as count from bet_info";
		}
		try {
			connection = db.connDB();
			preparedStatement= connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();

			if(resultSet.next()){
				count = resultSet.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}
		return count;
	}

	//�Խ��� ��ȯ
	public ArrayList gameList(GameDTO gameDTO) {
		ArrayList gameList = new ArrayList();
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null; 
		System.out.println("�� :gameList gameDTO.getselectpage() " +gameDTO.getSelectpage());

		String sql = "select pagenumber, game_seq, to_char(game_regdate,'YYYY-MM-DD')as game_regdate, hometeam, awayteam, game_check, game_result from" +
				"(select rownum as pagenumber, game_seq, game_regdate, hometeam, awayteam, game_check, game_result from" +
				"(select game_seq, game_regdate, hometeam, awayteam, game_check, game_result from GAME_SCHEDULE order by game_regdate desc)) " +
				"where pagenumber between ? and ?"; 

		if(gameDTO.getSelectpage().equals("game_schedule")){
			sql = "select pagenumber, game_seq, to_char(game_regdate,'YYYY-MM-DD')as game_regdate, hometeam, awayteam, game_check, game_result from" +
					"(select rownum as pagenumber, game_seq, game_regdate, hometeam, awayteam, game_check, game_result from" +
					"(select game_seq, game_regdate, hometeam, awayteam, game_check, game_result from GAME_SCHEDULE order by game_regdate desc)) " +
					"where pagenumber between ? and ?";
		}else if(gameDTO.getSelectpage().equals("bet_select")){
			sql = "select pagenumber, game_seq, to_char(game_regdate,'YYYY-MM-DD')as game_regdate, hometeam, awayteam, game_check, game_result from" +
					"(select rownum as pagenumber, game_seq, game_regdate, hometeam, awayteam, game_check, game_result from" +
					"(select game_seq, game_regdate, hometeam, awayteam, game_check, game_result from GAME_SCHEDULE where game_regdate >= sysdate order by game_regdate desc)) " +
					"where pagenumber between ? and ?";
		}
		try {
			connection = db.connDB();
			preparedStatement= connection.prepareStatement(sql);
			preparedStatement.setInt(1, gameDTO.getStartSeq());
			preparedStatement.setInt(2, gameDTO.getEndSeq());
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()){
				GameDTO gameListDTO = new GameDTO();
				gameListDTO.setGameSeq(Integer.parseInt(resultSet.getString("game_seq")));
				gameListDTO.setGameRegdate(resultSet.getString("game_regdate"));
				gameListDTO.setHomeTeam(resultSet.getString("hometeam"));
				gameListDTO.setAwayTeam(resultSet.getString("awayteam"));
				gameListDTO.setGameCheck(resultSet.getString("game_check"));
				if(resultSet.getString("game_result") != null) gameListDTO.setGameResult(resultSet.getString("game_result"));
				else gameListDTO.setGameResult(" ");
				gameList.add(gameListDTO);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}
		return gameList;
	}

	public int selectInsert(ArrayList gameList ,ArrayList gameSeqList, GameDTO gameDTO){
		int resultUadate = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		DBConnManager db = new DBConnManager();
		ResultSet resultSet = null;

		int GroupSeq = 0;
		String hometeam = "";
		String awayteam = "";
		String gameSeq = "";
		String gameSelect = "";
		Date gameDate= null;
		ArrayList selectList = new ArrayList();

		//����Ʈ���� ���� ������ �ִµ� betseq�� ���� groupseq�� leng�� ��ŭ �����ϰ� 

		String betGroupSeqSql = "select bet_group_seq.nextval as Groupseq from dual";

		String team = "select hometeam, awayteam, to_char(game_regdate,'YYYY-MM-DD')as game_regdate from game_schedule where game_seq =?";

		String sql = "insert into bet_info values(bet_seq.nextval, ?, ?, ?, ? , ?, ?, ?, ?,?,0)"; 

		try {
			connection = db.connDB();
			preparedStatement = connection.prepareStatement(betGroupSeqSql);
			resultSet =  preparedStatement.executeQuery();

			if(resultSet.next()){
				GroupSeq = resultSet.getInt("Groupseq");
			}

			//gameList�� ���������� 
			for(int i = 0; i < gameSeqList.size(); i++){
				if(gameList.get(i) != null){

					//gameSeq�̱�
					gameSeq = gameSeqList.get(i).toString();
					gameSeq = onlyNum(gameSeq);
					//Ȩ���� ������� �̾Ƴ���
					preparedStatement = connection.prepareStatement(team);
					preparedStatement.setString(1, gameSeq);
					resultSet =  preparedStatement.executeQuery();
					if(resultSet.next()){
						hometeam = resultSet.getString("hometeam");
						awayteam = resultSet.getString("awayteam");
						gameDate = resultSet.getDate("game_regdate");
					}
					gameSelect = gameList.get(i).toString();
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setInt(1, Integer.parseInt(gameSeq));
					preparedStatement.setString(2, gameSelect);
					preparedStatement.setInt(3, gameDTO.getPaymentSeq());
					preparedStatement.setInt(4, gameDTO.getMemberSeq());
					preparedStatement.setInt(5, GroupSeq);
					preparedStatement.setString(6, hometeam);
					preparedStatement.setString(7, awayteam);
					preparedStatement.setDate(8, gameDate);
					preparedStatement.setInt(9,gameDTO.getAllocation());
					resultUadate = preparedStatement.executeUpdate();
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement);
		}
		return GroupSeq;
	}

	//���ڿ����� ���ڸ� ����
	public String onlyNum(String str) {
		if ( str == null ) return "";

		StringBuffer sb = new StringBuffer();
		for(int i = 0; i < str.length(); i++){
			if( Character.isDigit( str.charAt(i))) {
				sb.append( str.charAt(i) );
			}
		}
		return sb.toString();
	} 

	//�ӽ��� member select
	public GameDTO memberSelect(){
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null; 
		GameDTO gameDTO = new GameDTO();
		String sql = "select member_seq from member";
		try {
			connection = db.connDB();
			preparedStatement= connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();

			if (resultSet.next()){
				gameDTO.setMemberSeq(resultSet.getInt("member_seq"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}

		return gameDTO;
	}
	//�ӽ��� payment select
	public GameDTO paymentSelect(){
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null; 
		GameDTO gameDTO = new GameDTO();
		String sql = "select payment_seq from payment";
		try {
			connection = db.connDB();
			preparedStatement= connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();

			if (resultSet.next()){
				gameDTO.setPaymentSeq(resultSet.getInt("payment_seq"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}

		return gameDTO;
	}

	public ArrayList selectView(GameDTO gameDTO) {
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		//����
		ArrayList selectValueList = new ArrayList();

		String sql = "select game_seq, bet_seq, select_value, hometeam, awayteam,to_char(regdate,'YYYY-MM-DD')as regdate,allocation,getmoney,bet_group_seq from bet_info where bet_group_seq = ?";
		try {
			connection = db.connDB();
			preparedStatement= connection.prepareStatement(sql);
			preparedStatement.setInt(1, gameDTO.getBetGroupSeq());
			resultSet = preparedStatement.executeQuery();

			while(resultSet.next()){
				GameDTO gameSelectDTO = new GameDTO();
				gameSelectDTO.setGameSeq(resultSet.getInt("game_seq"));
				gameSelectDTO.setBetSeq(resultSet.getInt("bet_seq"));
				gameSelectDTO.setSelectValue(resultSet.getString("select_value"));
				gameSelectDTO.setHomeTeam(resultSet.getString("hometeam"));
				gameSelectDTO.setAwayTeam(resultSet.getString("awayteam"));
				gameSelectDTO.setGameRegdate(resultSet.getString("regdate"));
				gameSelectDTO.setAllocation(resultSet.getInt("allocation"));
				gameSelectDTO.setGetMoney(resultSet.getInt("getmoney"));
				gameSelectDTO.setBetGroupSeq(resultSet.getInt("bet_group_seq"));
				selectValueList.add(gameSelectDTO);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}

		return selectValueList;
	}

	public ArrayList memberSelectView(GameDTO gameDTO) {
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null; 
		int flag = 0;
		ArrayList betSeqList = new ArrayList();
		ArrayList betInfoList = new ArrayList();
		ArrayList betGroupInfoList = null;
		ArrayList groupGroupInfoList = new ArrayList();
		String groupSql = "select distinct bet_group_seq  from bet_info where member_seq = ? order by bet_group_seq desc";

		String sql = "select bet_seq, game_seq, select_value, payment_seq, member_seq, " +
				"bet_group_seq,hometeam,awayteam,to_char(regdate,'YYYY-MM-DD')as regdate, allocation " +
				"from bet_info where member_seq = ? order by bet_group_seq";
		try {
			connection = db.connDB();
			preparedStatement= connection.prepareStatement(groupSql);
			preparedStatement.setInt(1, gameDTO.getMemberSeq());
			resultSet = preparedStatement.executeQuery();

			while(resultSet.next()){
				int betGroupSeq = resultSet.getInt("bet_group_seq");
				betSeqList.add(betGroupSeq);
			}

			preparedStatement= connection.prepareStatement(sql);
			preparedStatement.setInt(1, gameDTO.getMemberSeq());
			resultSet = preparedStatement.executeQuery();

			while(resultSet.next()){
				GameDTO betGameInfoDTO = new GameDTO();
				betGameInfoDTO.setBetSeq(resultSet.getInt("bet_seq"));
				betGameInfoDTO.setGameSeq(resultSet.getInt("game_seq"));
				betGameInfoDTO.setSelectValue(resultSet.getString("select_value"));
				betGameInfoDTO.setPaymentSeq(resultSet.getInt("payment_seq"));
				betGameInfoDTO.setMemberSeq(resultSet.getInt("member_seq"));
				betGameInfoDTO.setBetGroupSeq(resultSet.getInt("bet_group_seq"));
				betGameInfoDTO.setHomeTeam(resultSet.getString("hometeam"));
				betGameInfoDTO.setAwayTeam(resultSet.getString("awayteam"));
				betGameInfoDTO.setGameRegdate(resultSet.getString("regdate"));
				betGameInfoDTO.setAllocation(resultSet.getInt("allocation"));
				betInfoList.add(betGameInfoDTO);
			}
			for(int i = 0; i < betSeqList.size(); i++){
				betGroupInfoList = new ArrayList();
				for(int j = 0; j < betInfoList.size(); j++){
					if(Integer.parseInt(betSeqList.get(i).toString()) == ((GameDTO)betInfoList.get(j)).getBetGroupSeq()){
						GameDTO betGroupInfoDTO = new GameDTO();

						betGroupInfoDTO.setBetSeq(((GameDTO)betInfoList.get(j)).getBetSeq());
						betGroupInfoDTO.setGameSeq(((GameDTO)betInfoList.get(j)).getGameSeq());
						betGroupInfoDTO.setSelectValue(((GameDTO)betInfoList.get(j)).getSelectValue());
						betGroupInfoDTO.setPaymentSeq(((GameDTO)betInfoList.get(j)).getPaymentSeq());
						betGroupInfoDTO.setMemberSeq(((GameDTO)betInfoList.get(j)).getMemberSeq());
						betGroupInfoDTO.setBetGroupSeq(((GameDTO)betInfoList.get(j)).getBetGroupSeq());
						betGroupInfoDTO.setHomeTeam(((GameDTO)betInfoList.get(j)).getHomeTeam());
						betGroupInfoDTO.setAwayTeam(((GameDTO)betInfoList.get(j)).getAwayTeam());
						betGroupInfoDTO.setGameRegdate(((GameDTO)betInfoList.get(j)).getGameRegdate());
						betGroupInfoDTO.setAllocation(((GameDTO)betInfoList.get(j)).getAllocation());
						betGroupInfoList.add(betGroupInfoDTO);
						flag = 1;
					}
				}
				if(flag == 1){
					groupGroupInfoList.add(betGroupInfoList);
					flag = 0;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}

		return groupGroupInfoList;
	}

	public ArrayList selectCompare(ArrayList gameSelectList) {
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null; 
		ArrayList selectValueList = new ArrayList();
		//���� ���� 
		String sql = "select game_seq, to_char(game_regdate,'YYYY-MM-DD')as game_regdate, hometeam, awayteam, game_check, game_result from GAME_SCHEDULE where game_seq =?";
		try {
			connection = db.connDB();
			for(int i = 0; i < gameSelectList.size(); i ++){
				preparedStatement= connection.prepareStatement(sql);
				preparedStatement.setInt(1, ((GameDTO)gameSelectList.get(i)).getGameSeq());
				resultSet = preparedStatement.executeQuery();

				if(resultSet.next()){
					GameDTO gameResultDTO = new GameDTO();
					gameResultDTO.setGameSeq(resultSet.getInt("game_seq"));
					gameResultDTO.setHomeTeam(resultSet.getString("hometeam"));
					gameResultDTO.setAwayTeam(resultSet.getString("awayteam"));
					gameResultDTO.setGameResult(resultSet.getString("game_result"));
					gameResultDTO.setGameRegdate(resultSet.getString("game_regdate"));
					gameResultDTO.setAllocation(((GameDTO)gameSelectList.get(0)).getAllocation());
					gameResultDTO.setGetMoney(((GameDTO)gameSelectList.get(0)).getGetMoney());
					gameResultDTO.setBetGroupSeq(((GameDTO)gameSelectList.get(0)).getBetGroupSeq());
					//���� �����ѰͰ� ��� �� 
					if( !(resultSet.getString("game_result").equals("before"))){
						if(((GameDTO)gameSelectList.get(i)).getSelectValue().equals(resultSet.getString("game_result"))){
							gameResultDTO.setSuccessSelect("pass");
						}else {
							gameResultDTO.setSuccessSelect("fail");
						}
					}else{
						gameResultDTO.setSuccessSelect("before");
					}

					selectValueList.add(gameResultDTO);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}


		return selectValueList;
	}

	public int insertShedule(GameDTO gameDTO) {
		int resultInsert = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		DBConnManager db = new DBConnManager();

		String sql = "INSERT INTO game_schedule " +
				"values(game_seq.nextval,?,?,?,?,?)";
		try {
			connection = db.connDB();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, gameDTO.getGameRegdate());
			preparedStatement.setString(2, gameDTO.getHomeTeam());
			preparedStatement.setString(3, gameDTO.getAwayTeam());
			preparedStatement.setString(4, gameDTO.getGameCheck());
			preparedStatement.setString(5, gameDTO.getGameResult());

			resultInsert = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement);
		}
		return resultInsert;
	}

	public int sheduleDelete(GameDTO gameDTO) {
		int resultDelete = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		DBConnManager db = new DBConnManager();

		String sql = "DELETE FROM game_schedule WHERE game_seq = ?";
		try {
			connection = db.connDB();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, gameDTO.getGameSeq());
			resultDelete = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement);
		}
		return resultDelete;
	}

	public ArrayList listSummary(){
		ArrayList gameList = new ArrayList();
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null; 
		int count = 0;

		String sql = "select game_seq, to_char(game_regdate,'YYYY-MM-DD')as game_regdate, hometeam, awayteam, game_check, game_result from GAME_SCHEDULE order by game_regdate desc";

		try {
			connection = db.connDB();
			preparedStatement= connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()){
				count++;
				if(count > 4) break;
				GameDTO gameListDTO = new GameDTO();
				gameListDTO.setGameSeq(Integer.parseInt(resultSet.getString("game_seq")));
				gameListDTO.setGameRegdate(resultSet.getString("game_regdate"));
				gameListDTO.setHomeTeam(resultSet.getString("hometeam"));
				gameListDTO.setAwayTeam(resultSet.getString("awayteam"));
				gameListDTO.setGameCheck(resultSet.getString("game_check"));
				if(resultSet.getString("game_result") != null) gameListDTO.setGameResult(resultSet.getString("game_result"));
				else gameListDTO.setGameResult(" ");
				gameList.add(gameListDTO);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}
		return gameList;
	}

	public int checkGetMoney(int getGropSeq){
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null; 
		int result = 0;
		String sql = "update bet_info set getmoney = 1 where bet_group_seq = ?";
		try {
			connection = db.connDB();
			preparedStatement= connection.prepareStatement(sql);
			preparedStatement.setInt(1,getGropSeq);
			result = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}
		return result;
	}

	public int sheduleUpdate(GameDTO gameDTO) {
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null; 
		int result = 0;
		String sql = "update game_schedule set game_check = ?, hometeam = ?, awayteam = ?,game_regdate =?, game_result =?  where game_seq = ?";
		try {
			connection = db.connDB();
			preparedStatement= connection.prepareStatement(sql);
			preparedStatement.setString(1,gameDTO.getGameCheck());
			preparedStatement.setString(2,gameDTO.getHomeTeam());
			preparedStatement.setString(3,gameDTO.getAwayTeam());
			preparedStatement.setString(4,gameDTO.getGameRegdate());
			preparedStatement.setString(5,gameDTO.getGameResult());
			preparedStatement.setInt(6,gameDTO.getGameSeq());
			result = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}
		return result;
	}

	public GameDTO clickSchedule(int gameSeq) {
		DBConnManager db = new DBConnManager();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		GameDTO gameSelectDTO = new GameDTO();

		String sql = "select game_seq, game_check, hometeam, awayteam,game_regdate,game_result from game_schedule where game_seq = ?";
		try {
			connection = db.connDB();
			preparedStatement= connection.prepareStatement(sql);
			preparedStatement.setInt(1, gameSeq);
			resultSet = preparedStatement.executeQuery();
			

			if(resultSet.next()){
				gameSelectDTO.setGameSeq(resultSet.getInt("game_seq"));
				gameSelectDTO.setHomeTeam(resultSet.getString("hometeam"));
				gameSelectDTO.setAwayTeam(resultSet.getString("awayteam"));
				gameSelectDTO.setGameRegdate(resultSet.getString("game_regdate"));
				gameSelectDTO.setGameCheck(resultSet.getString("game_check"));
				gameSelectDTO.setGameResult(resultSet.getString("game_result"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeDB(connection, preparedStatement, resultSet);
		}

		return gameSelectDTO;
	}
}
